package com.example.myapplication;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class BookDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "BookDatabase";
    private static final int DATABASE_VERSION = 1;
    public static final String TABLE_BOOKS = "books";
    public static final String COLUMN_ID_BOOK = "_id";
    public static final String COLUMN_TITLE = "title";
    private static final String COLUMN_AUTHOR = "author";
    private static final String COLUMN_PRICE = "price";
    private static final String COLUMN_PUBLISHER = "publisher";
    private static final String COLUMN_REMARKS = "remarks";
    public BookDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTableQuery = "CREATE TABLE " + TABLE_BOOKS + " (" +
                COLUMN_ID_BOOK + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_TITLE + " TEXT, " +
                COLUMN_AUTHOR + " TEXT, " +
                COLUMN_PRICE + " TEXT, " +
                COLUMN_PUBLISHER + " TEXT, " +
                COLUMN_REMARKS + " TEXT)";
        db.execSQL(createTableQuery);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKS);
        onCreate(db);
    }

    public long addBook(String title, String author, String price, String publisher, String remarks) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, title);
        values.put(COLUMN_AUTHOR, author);
        values.put(COLUMN_PRICE, price);
        values.put(COLUMN_PUBLISHER, publisher);
        values.put(COLUMN_REMARKS, remarks);
        long result = db.insert(TABLE_BOOKS, null, values);
//        db.close();
        return result;
    }
    public List<Book> getAllBooks() {
        List<Book> bookList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        // 修正列名以匹配数据库模式
        Cursor cursor = db.query(TABLE_BOOKS,
                new String[]{COLUMN_ID_BOOK, COLUMN_TITLE, COLUMN_AUTHOR, COLUMN_PRICE, COLUMN_PUBLISHER, COLUMN_REMARKS},
                null, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                Book book = new Book();
                book.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID_BOOK)));
                book.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                book.setAuthor(cursor.getString(cursor.getColumnIndex(COLUMN_AUTHOR)));
                book.setPrice(cursor.getString(cursor.getColumnIndex(COLUMN_PRICE)));
                book.setPublisher(cursor.getString(cursor.getColumnIndex(COLUMN_PUBLISHER)));
                book.setRemark(cursor.getString(cursor.getColumnIndex(COLUMN_REMARKS)));
                bookList.add(book);
            } while (cursor.moveToNext());
        }
//        cursor.close();
//        db.close();
        return bookList;
    }
    /**
     * 根据书名获取书籍ID
     */
    public int getBookIdByTitle(String title) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_BOOKS,
                new String[]{COLUMN_ID_BOOK},
                COLUMN_TITLE + " = ?",
                new String[]{title},
                null, null, null
        );

        int bookId = -1;
        if (cursor.moveToFirst()) {
            bookId = cursor.getInt(cursor.getColumnIndex(COLUMN_ID_BOOK));
        }

        cursor.close();
        db.close();
        return bookId;
    }

    /**
     * 检查书籍是否存在
     */
    public boolean isBookAvailable(String title) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_BOOKS,
                new String[]{COLUMN_ID_BOOK},
                COLUMN_TITLE + " = ?",
                new String[]{title},
                null, null, null
        );

        boolean exists = cursor.getCount() > 0;
        cursor.close();
        db.close();
        return exists;
    }
}